NULL and Blank Handling

Salesforce does not have the concept of NULL. A text field can be blank/empty but not NULL. Similarly, a checkbox can be either checked or unchecked, but not NULL. Understanding how Speediful process NULL values and empty strings is crucial for achieving the desired data outcomes in Salesforce. Speediful aims for compatibility with existing conventions across different Salesforce APIs.

The way NULL values and empty strings are handled differs between the SOAP API and Bulk APIs due to the underlying capabilities of each Salesforce API.

API Comparison Table

Value Type	Bulk APIs	SOAP (ignore_nulls=0)	SOAP (ignore_nulls=1)
NULL	Blanks field	Blanks field	Preserves data on update / Default values on insert
Empty string ('')	Preserves data on update / Default values on insert	Blanks field	Blanks field
#N/A string ('#N/A')	Blanks field	Sets field to '#N/A'	Sets field to '#N/A'
Whitespace or NBSP (' ')	Writes to SF	Writes to SF	Writes to SF

SOAP API Behavior

While SQL data is structured with the same set of columns for each record, the SOAP API is flexible in that it allows each record in a request to be sent with a different subset of fields. For example, record 1 can include (Name, Phone, Website) while record 2 can include (Name, Description, AnnualRevenue). By default, Speediful will send all columns that are eligible for loading, but it can be set to omit fields with the NULL value.

Default Behavior (@ignore_nulls = 0)

By default, NULL values and empty strings are processed the same way via the SOAP API:

• NULL value: Sets the field to blank in Salesforce (behaves like empty string)
• Empty string (''): Sets the field to blank in Salesforce

This means that both NULL and empty string will explicitly blank out the field value in Salesforce.

When to use: You want to send the whole row, and are ok with blanking out existing data or default values when the source table contains NULLs

Ignore Nulls Enabled (@ignore_nulls = 1)

When @ignore_nulls is set to 1:

• NULL values: Are ignored and not sent to Salesforce - existing data or field defaults are preserved
• Empty string (''): Still sets the field to blank in Salesforce

This option allows you to update only the fields with actual data while preserving existing values for NULL fields.

When to use: A good situation to use @ignore_nulls = 1 is when you have sparse data updates to process across a number of fields and don't want to overwrite other fields. For example, communication or subscription preference data. For example, sending this data in a SOAP update with @ignore_nulls=1 would have the effect noted in the final column:

External_Id__c	Opt_In__c	Monthly_Email__c	Surveys__c	Effect
1	NULL	1	NULL	Update Monthly_Email__c without changing Opt_In__c or Surveys__c
2	0	NULL	NULL	Update Opt_In__c without changing Monthly_Email__c or Surveys__c

Bulk API Behavior (Bulk v1 and Bulk v2)

The Bulk APIs do not support the @ignore_nulls parameter. The whole row is always sent and the behavior is fixed:

• NULL value: Sets the field to blank in Salesforce
• #N/A string ('#N/A'): Sets the field to blank in Salesforce (This is a Salesforce special value)
• Empty string (''): Has no effect - existing data or field defaults are preserved

Important Notes

What Counts as an Empty String?

An empty string is exactly '' - a zero-length string. The following are NOT empty strings and will write their data to Salesforce:

• Strings containing whitespace (spaces, tabs, etc.)
• Strings containing non-breaking spaces (NBSP)
• Any string with a length greater than zero

Default Values

Salesforce only sets default values during record insert operations, and only if the field is not being set to blank. Default values are not applied during update operations, regardless of whether fields are blanked or omitted.

Best Practices

1. Backup data to establish a rollback snapshot before performing a data load

2. Be explicit for update/upsert operations:

   • NULL is the consistent way to blank out data across the APIs
   • Ensure you know how NULL and blank data in your source dataset should be interpreted and handled
   • Use the backup data to provide defaults when preparing the data to be loaded

3. Check your data to ensure no unintended whitespace is being treated as real data

4. Test Before Production to validate the results of your intended operations